package qc.module.ehs.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import qc.common.core.enums.EhsHazardKeyTypeEnum;
import qc.common.core.exception.QCPromptException;
import qc.common.core.unify.QCUnifyReturnValue;
import qc.common.core.utils.IdentifierUtil;
import qc.module.ehs.dto.templatehazard.*;
import qc.module.ehs.entity.EhsTplHazardcategory;
import qc.module.ehs.entity.EhsTplHazarddb;
import qc.module.ehs.entity.EhsTplHazarditem;
import qc.module.ehs.entity.EhsTplHazardproject;
import qc.module.ehs.mapper.EhsTplHazarddbMapper;
import qc.module.ehs.repository.EhsTplHazardcategoryRepository;
import qc.module.ehs.repository.EhsTplHazarddbRepository;
import qc.module.ehs.repository.EhsTplHazarditemRepository;
import qc.module.ehs.repository.EhsTplHazardprojectRepository;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 危险源导则库接口Service
 *
 * @author QuCheng Tech
 * @create 2024/5/22
 */
@Service
public class EhsTplHazarddbService {
    
    private EhsTplHazarddbRepository repository;

    @Autowired
    public void setRepository(EhsTplHazarddbRepository repository) {
        this.repository = repository;
    }

    @Autowired
    private EhsTplHazardcategoryRepository hazardcategoryRepository;

    @Autowired
    private EhsTplHazardcategoryService hazardcategoryService;

    @Autowired
    private EhsTplHazardprojectRepository hazardprojectRepository;

    @Autowired
    private EhsTplHazardprojectService hazardprojectService;

    @Autowired
    private EhsTplHazarditemRepository hazarditemRepository;

    @Autowired
    private EhsTplHazarditemService hazarditemService;

    /**
     * 查询危险源导则库
     *
     * @param condition 查询条件
     * @return List<TplHazardDbDto>
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public List<TplHazardDbDto> queryDB(TplHazardDbQueryConditionDto condition) throws QCPromptException {
        //1.构造查询导则库条件
        LambdaQueryWrapper<EhsTplHazarddb> wrapper = new LambdaQueryWrapper<>();
        //阶段标记：为空或错误表示不管阶段，为1表示在建阶段，为0表时运行阶段
        if (StringUtils.isNotBlank(condition.getPeriod())){
            if (condition.getPeriod().equals("1")){
                wrapper.eq(EhsTplHazarddb::getIsconstruction,true);
            }else if (condition.getPeriod().equals("0")){
                wrapper.eq(EhsTplHazarddb::getIsconstruction,false);
            }
        }
        //导则库代码
        if (StringUtils.isNotBlank(condition.getHdbcd())){
            wrapper.eq(EhsTplHazarddb::getHdbcd,condition.getHdbcd());
        }
        //关键字：导则库代码和名称模糊匹配
        if (StringUtils.isNotBlank(condition.getKeywords())){
            wrapper.and(q -> {
                q.like(EhsTplHazarddb::getHdbcd, condition.getKeywords()).or()
                        .like(EhsTplHazarddb::getHdbnm, condition.getKeywords());
            });
        }
        //查询的关键字类型：查询的关键是类别、项目还是危险源清单
        if (StringUtils.isNotBlank(condition.getKeytype())){
            if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.CATEGORY.getIndex()){
                List<String> hdbcds = new ArrayList<>();
                //判断标签
                if (StringUtils.isNotBlank(condition.getTags())){
                    //获取类别中的导则库代码集合
                    hdbcds = hazardcategoryService.getHdbcds(null,Integer.parseInt(condition.getTags()));
                }else{
                    hdbcds = hazardcategoryService.getHdbcds(null,null);
                }
                if (hdbcds == null || hdbcds.size() == 0x0) return null;
                wrapper.in(EhsTplHazarddb::getHdbcd,hdbcds);
            } else if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.PROJECT.getIndex()) {
                //判断标签
                List<String> hcatcds = new ArrayList<>();
                //获取项目中的类别代码集合
                if (StringUtils.isNotBlank(condition.getTags())){
                    hcatcds = hazardprojectService.getHcatcds(null,Integer.parseInt(condition.getTags()));
                }else{
                    hcatcds = hazardprojectService.getHcatcds(null,null);
                }
                //根据类别代码集合获取导则库代码集合
                if (hcatcds == null || hcatcds.size() == 0x0) return null;
                List<String> hdbcds = hazardcategoryService.getHdbcds(hcatcds,null);
                wrapper.in(EhsTplHazarddb::getHdbcd,hdbcds);
            } else if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.ITEM.getIndex()) {
                if (StringUtils.isNotBlank(condition.getTags())) return null;//清单中无标签
                //获取清单中的项目代码集合
                List<String> hpjtcds = hazarditemService.getHpjtcds();
                if (hpjtcds == null || hpjtcds.size() == 0x0) return null;
                //根据项目代码集合获取类别代码集合
                List<String> hcatcds = hazardprojectService.getHcatcds(hpjtcds,null);
                if (hcatcds == null || hcatcds.size() == 0x0) return null;
                //根据类别代码集合获取导则库代码集合
                List<String> hdbcds = hazardcategoryService.getHdbcds(hcatcds,null);
                if (hdbcds == null || hdbcds.size() == 0x0) return null;
                wrapper.in(EhsTplHazarddb::getHdbcd,hdbcds);
            }
        }
        //排序
        wrapper.orderByAsc(EhsTplHazarddb::getHdbcd);
        
        //2.查询数据并返回
        List<EhsTplHazarddb> ens = repository.selectList(wrapper);
        if (CollectionUtils.isEmpty(ens)) return null;
        return EhsTplHazarddbMapper.MAPPER.toDtoList(ens);
    }

    /**
     * 查询导则库危险源清单TreeTable
     * 查询指定危险源导则库中的危险源清单，以TreeTable形式返回数据；可以指定类别、项目；返回结果中按照类别--项目--危险源层级结构进行返回；
     *
     * @param condition 查询条件
     * @return List<TplHazardTreeTableItemDto>
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public List<TplHazardTreeTableItemDto> queryTreeTable(TplHazardDbQueryConditionDto condition) throws QCPromptException {
        //1。查询条件判空
        if (condition == null) throw new QCPromptException("查询条件不能为空");
        if (StringUtils.isBlank(condition.getHdbcd())) throw new QCPromptException("导则库代码不能为空");
        
        //2.根据危险源导则库代码获取危险源清单树
        List<TplHazardTreeTableItemDto> result = handleTreeTable(condition.getHdbcd());
        if (CollectionUtils.isEmpty(result)) return result;
        
        //3.根据不同查询条件从清单树中筛选结果
        //3.1 关键字，模糊匹配代码和名称
        if (StringUtils.isNotBlank(condition.getKeywords())){
            if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.CATEGORY.getIndex()){
                //3.1.1 匹配类别，关键字模糊匹配类别的代码和名称，不匹配的类别从结果集中移除
                Iterator<TplHazardTreeTableItemDto> level1Iterator = result.iterator();
                while (level1Iterator.hasNext()){
                    TplHazardTreeTableItemDto level1 = level1Iterator.next();
                    if (!(level1.getCode().contains(condition.getKeywords())) && !(level1.getName().contains(condition.getKeywords()))){
                        level1Iterator.remove();
                    }
                }
            }else if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.PROJECT.getIndex()){
                //匹配项目：关键字模糊匹配项目的代码和名称，不匹配的项目和类别从结果集中移除
                Iterator<TplHazardTreeTableItemDto> level1Iterator = result.iterator();
                while (level1Iterator.hasNext()){
                    TplHazardTreeTableItemDto level1 = level1Iterator.next();
                    if (level1.getChildren().size() > 0x0){
                        Iterator<TplHazardTreeTableItemDto> level2Iterator = level1.getChildren().iterator();
                        while (level2Iterator.hasNext()){
                            TplHazardTreeTableItemDto level2 = level2Iterator.next();
                            if (!(level2.getCode().contains(condition.getKeywords())) && !(level2.getName().contains(condition.getKeywords()))){
                                level2Iterator.remove();
                            }
                        }
                    }
                    if (level1.getChildren().size() == 0x0) level1Iterator.remove();
                }
            }else if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.ITEM.getIndex()){
                //匹配清单：关键字模糊匹配清单的代码和名称，不匹配的清单、项目和类别从结果集中移除
                Iterator<TplHazardTreeTableItemDto> level1Iterator = result.iterator();
                while (level1Iterator.hasNext()){
                    TplHazardTreeTableItemDto level1 = level1Iterator.next();
                    if (level1.getChildren().size() > 0x0){
                        Iterator<TplHazardTreeTableItemDto> level2Iterator = level1.getChildren().iterator();
                        while (level2Iterator.hasNext()){
                            TplHazardTreeTableItemDto level2 = level2Iterator.next();
                            if (level2.getChildren().size() > 0x0){
                                Iterator<TplHazardTreeTableItemDto> level3Iterator = level2.getChildren().iterator();
                                while (level3Iterator.hasNext()){
                                    TplHazardTreeTableItemDto level3 = level3Iterator.next();
                                    if (!(level3.getCode().contains(condition.getKeywords())) && !(level3.getName().contains(condition.getKeywords()))){
                                        level3Iterator.remove();
                                    }
                                }
                            }
                            if (level2.getChildren().size() == 0x0) level2Iterator.remove();
                        }
                    }
                    if (level1.getChildren().size() == 0x0) level1Iterator.remove();
                }
            }
        }
        
        //2.2 标签：与类别、项目中的tags一致
        if (StringUtils.isNotBlank(condition.getTags())){
            if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.CATEGORY.getIndex()){
                //类别，匹配类别中的tags，不匹配的类别从结果集中移除
                Iterator<TplHazardTreeTableItemDto> level1Iterator = result.iterator();
                while (level1Iterator.hasNext()){
                    TplHazardTreeTableItemDto level1 = level1Iterator.next();
                    if (level1.getTags() != null && level1.getTags() != Integer.parseInt(condition.getTags())){
                        level1Iterator.remove();
                    }
                }
            }else if (Integer.parseInt(condition.getKeytype()) == EhsHazardKeyTypeEnum.PROJECT.getIndex()){
                //项目：匹配项目中的tags，不匹配的项目和类别从结果集中移除
                Iterator<TplHazardTreeTableItemDto> level1Iterator = result.iterator();
                while (level1Iterator.hasNext()){
                    TplHazardTreeTableItemDto level1 = level1Iterator.next();
                    if (level1.getChildren().size() > 0x0){
                        Iterator<TplHazardTreeTableItemDto> level2Iterator = level1.getChildren().iterator();
                        while (level2Iterator.hasNext()){
                            TplHazardTreeTableItemDto level2 = level2Iterator.next();
                            if (level2.getTags() != null && level2.getTags() != Integer.parseInt(condition.getTags())){
                                level2Iterator.remove();
                            }
                        }
                    }
                    if (level1.getChildren().size() == 0x0) level1Iterator.remove();
                }
            }
        }
        
        //3.返回结果集
        return result;
    }
    
    /**
     * 获取指定危险源导则库完整信息
     *
     * @param id 危险源导则库代码
     * @return TplHazardDbFullInfoDto
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public TplHazardDbFullInfoDto getDBFull(String id) throws QCPromptException {
        if (StringUtils.isBlank(id)) throw new QCPromptException("危险源导则库代码不能为空");

        TplHazardDbFullInfoDto result = new TplHazardDbFullInfoDto();
        
        //查询危险源导则库信息
        EhsTplHazarddb db = repository.selectById(id);
        TplHazardDbDto dbDto = EhsTplHazarddbMapper.MAPPER.toDto(db);
        result.setDb(dbDto);

        List<TplHazardTreeTableItemDto> items = handleTreeTable(id);
        result.setItems(items);
        return result;
    }

    /**
     * 处理指定危险源清单
     * 以TreeTable形式返回数据；可以指定类别、项目；返回结果中按照类别--项目--危险源层级结构进行返回；
     * 类别：层级1，项目：层级2，清单：层级3
     * 
     * @param id 危险源导则库代码
     * @return List<TplHazardTreeTableItemDto>
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    private List<TplHazardTreeTableItemDto> handleTreeTable(String id) throws QCPromptException {
        //返回结果集
        List<TplHazardTreeTableItemDto> items = new ArrayList<>();

        //获取指定导则库类别，作为treeTable第一层
        List<TplHazardCategoryDto> categoryDtos = hazardcategoryService.getDBCategories(id);
        if (CollectionUtils.isNotEmpty(categoryDtos)){
            for (TplHazardCategoryDto categoryDto : categoryDtos){
                TplHazardTreeTableItemDto level1Item = new TplHazardTreeTableItemDto();
                level1Item.setCode(categoryDto.getHcatcd());
                level1Item.setName(categoryDto.getHcatnm());
                level1Item.setLevel(0x1);
                level1Item.setOdr(categoryDto.getOdr());
                level1Item.setTags(categoryDto.getTags());

                //获取当前导则库类别的项目，作为treeTable第二层
                List<TplHazardProjectDto> projectDtos = hazardprojectService.getCategoryProjects(categoryDto.getHcatcd());
                if (CollectionUtils.isNotEmpty(projectDtos)){
                    List<TplHazardTreeTableItemDto> level2Items = new ArrayList<>();
                    for (TplHazardProjectDto projectDto : projectDtos){
                        TplHazardTreeTableItemDto level2Item = new TplHazardTreeTableItemDto();
                        level2Item.setCode(projectDto.getHpjtcd());
                        level2Item.setName(projectDto.getHpjtnm());
                        level2Item.setLevel(0x2);
                        level2Item.setOdr(projectDto.getOdr());
                        level2Item.setTags(projectDto.getTags());

                        //获取当前导则库项目的清单，作为treeTable第三层
                        List<TplHazardItemDto> itemDtos = hazarditemService.getProjectItems(projectDto.getHpjtcd());
                        if (CollectionUtils.isNotEmpty(itemDtos)){
                            List<TplHazardTreeTableItemDto> level3Items = new ArrayList<>();
                            for (TplHazardItemDto itemDto : itemDtos){
                                TplHazardTreeTableItemDto level3Item = new TplHazardTreeTableItemDto();
                                level3Item.setCode(itemDto.getHicd());
                                level3Item.setName(itemDto.getHinm());
                                level3Item.setLevel(0x3);
                                level3Item.setIsmajor(itemDto.getIsmajor());
                                level3Item.setCause(itemDto.getCause());
                                level3Item.setConsequence(itemDto.getConsequence());
                                level3Item.setPrevent(itemDto.getPrevent());
                                level3Item.setEmergency(itemDto.getEmergency());
                                level3Item.setLmin(itemDto.getLmin());
                                level3Item.setLmax(itemDto.getLmax());
                                level3Item.setEmin(itemDto.getEmin());
                                level3Item.setEmax(itemDto.getEmax());
                                level3Item.setCmin(itemDto.getCmin());
                                level3Item.setCmax(itemDto.getCmax());
                                level1Item.setDescription(itemDto.getDescription());

                                level3Items.add(level3Item);
                            }
                            level2Item.setChildren(level3Items);
                        }
                        level2Items.add(level2Item);
                    }
                    level1Item.setChildren(level2Items);
                }
                items.add(level1Item);
            }
        }
        return items;
    }

    /**
     * 导入危险源导则库
     *
     * @param file 导入处理的文件
     * @param dto 导入条件
     * @return 成功返回null，失败返回错误或提示信息
     * @author QuCheng Tech
     * @since 2024/5/27
     * @description 2024/06/12 功能代码修改 类别、项目、清单的代码使用UUID。
     */
    public String importDB(File file,TplHazardImportDto dto) throws Exception {
        
        FileInputStream in = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(in);
        Sheet sheet = workbook.getSheet("Sheet1");

        //表头信息<列下标，表头>
        Map<Integer,String> columns = new HashMap<>();

        //保存危险源类别信息
        List<TplHazardCategoryDto> categoryDtos = new ArrayList<>();
        //保存危险源项目信息
        List<TplHazardProjectDto> projectDtos = new ArrayList<>();
        //危险源清单信息
        List<TplHazardItemDto> itemDtos = new ArrayList<>();

        //查询当前导则库已有的类别
        List<TplHazardCategoryDto> dbCategories = hazardcategoryService.getDBCategories(dto.getHdbcd());
        //当前所有类别下已有的项目
        List<TplHazardProjectDto> categoryProjects = new ArrayList<>();
        //当前所有项目下已有的清单
        List<TplHazardItemDto> projectItems = new ArrayList<>();
        

        //（存在单元格合并）记录当前行的类别、项目编码、事故诱因、可能导致的后果
        String curentCategory = "";//导则库代码_类别代码
        String curentProject = "";//导则库代码_类别代码_项目代码
        String currentCase = "";
        String currentConsequence = "";

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            System.out.println("rowNum：" + row.getRowNum());
            Iterator<Cell> cellIterator = row.cellIterator();
            //当前行清单信息
            TplHazardItemDto itemDto = new TplHazardItemDto();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                System.out.println("列：" + cell);
                if (cell.toString().contains("类别") || cell.toString().contains("项目") ||
                        cell.toString().equals("危险源") || cell.toString().equals("一般危险源") || cell.toString().equals("重大危险源") ||
                        cell.toString().equals("事故诱因") || cell.toString().equals("可能导致的后果") || cell.toString().equals("可能导致的事故类型") ||
                        cell.toString().equals("L值范围") || cell.toString().equals("E值范围") || cell.toString().equals("S值或C值范围") ||
                        cell.toString().equals("L") || cell.toString().equals("E") || cell.toString().equals("C")
                ){
                    //记录需要取值的列下标及列名
                    columns.put(cell.getColumnIndex(),cell.toString());
                }else{
                    //处理需要保存的类别、项目及清单
                    if (columns.containsKey(cell.getColumnIndex())){
                        if (columns.get(cell.getColumnIndex()).equals("类别")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                TplHazardCategoryDto categoryDto = new TplHazardCategoryDto();
                                //判断导入方式如果是覆盖，重新生成类别代码
                                if (dto.getIscover()){
                                    curentCategory = IdentifierUtil.randomUUID();
                                }else{
                                    //判断当前类别是否已经存在，存在类别代码不生成新的，设置curentCategory即可，不存在则生成新的类别代码
                                    List<TplHazardCategoryDto> category = dbCategories.stream().filter(p -> p.getHcatnm().equals(cell.toString())).collect(Collectors.toList());
                                    if (category == null || category.size() == 0x0){
                                        //curentCategory = dto.getHdbcd() + "_" + ChineseCharacterUtil.getPinyinInitial(cell.toString());
                                        curentCategory = IdentifierUtil.randomUUID();
                                    }else {
                                        curentCategory = category.get(0x0).getHcatcd();
                                    } 
                                }
                                categoryDto.setHcatcd(curentCategory);
                                categoryDto.setHcatnm(cell.toString());
                                categoryDto.setHdbcd(dto.getHdbcd());
                                categoryDtos.add(categoryDto);
                                //获取每个类别的项目，添加到categoryProjects中
                                List<TplHazardProjectDto> projectDtoList = hazardprojectService.getCategoryProjects(categoryDto.getHcatcd());
                                if (CollectionUtils.isNotEmpty(projectDtoList)) categoryProjects.addAll(projectDtoList);
                                
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("项目")) {
                            if (StringUtils.isNotBlank(cell.toString())){
                                TplHazardProjectDto projectDto = new TplHazardProjectDto();
                                //判断导入方式如果是覆盖，重新生成项目代码
                                if (dto.getIscover()){
                                    curentProject = IdentifierUtil.randomUUID();
                                }else{
                                    //判断当前项目是否已经存在，存在类别代码不生成新的，设置curentProject即可
                                    List<TplHazardProjectDto> project = categoryProjects.stream().filter(p -> p.getHpjtnm().equals(cell.toString())).collect(Collectors.toList());
                                    if (project == null || project.size() == 0x0){
                                        //curentProject = curentCategory + "_" + ChineseCharacterUtil.getPinyinInitial(cell.toString());
                                        curentProject = IdentifierUtil.randomUUID();

                                    }else{
                                        curentProject = project.get(0x0).getHpjtcd();
                                    }
                                }
                                projectDto.setHpjtcd(curentProject);
                                projectDto.setHpjtnm(cell.toString());
                                projectDto.setHcatcd(curentCategory);
                                projectDtos.add(projectDto);
                                //获取每个项目的清单，添加到projectItems中
                                List<TplHazardItemDto> itemDtoList = hazarditemService.getProjectItems(projectDto.getHpjtcd());
                                if (CollectionUtils.isNotEmpty(itemDtoList)) projectItems.addAll(itemDtoList);
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("事故诱因")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                currentCase = cell.toString();
                            }
                            itemDto.setCause(currentCase);
                        }
                        else if (columns.get(cell.getColumnIndex()).equals("可能导致的后果") || columns.get(cell.getColumnIndex()).equals("可能导致的事故类型")) {
                            if (StringUtils.isNotBlank(cell.toString())){
                                currentConsequence = cell.toString();
                            }
                            itemDto.setConsequence(currentConsequence);
                        }else if (columns.get(cell.getColumnIndex()).equals("L值范围") || columns.get(cell.getColumnIndex()).equals("L")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                String[] split = cell.toString().split("～");
                                if (split != null && split.length == 0x2){
                                    itemDto.setLmin(Double.parseDouble(split[0]));
                                    itemDto.setLmax(Double.parseDouble(split[1]));
                                }
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("E值范围") || columns.get(cell.getColumnIndex()).equals("E")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                String[] split = cell.toString().split("～");
                                if (split != null && split.length == 0x2){
                                    itemDto.setEmin(Double.parseDouble(split[0]));
                                    itemDto.setEmax(Double.parseDouble(split[1]));
                                }
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("S值或C值范围") || columns.get(cell.getColumnIndex()).equals("C")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                String[] split = cell.toString().split("～");
                                if (split != null && split.length == 0x2){
                                    itemDto.setCmin(Double.parseDouble(split[0]));
                                    itemDto.setCmax(Double.parseDouble(split[1]));
                                }
                            }
                        }
                        
                        if(columns.containsValue("危险源") && columns.containsValue("一般危险源")){
                            if (columns.get(cell.getColumnIndex()).equals("危险源")){
                                if (StringUtils.isNotBlank(cell.toString())){
                                    //判断导入方式如果是覆盖，重新生成清单代码
                                    if (dto.getIscover()) {
                                        itemDto.setHicd(IdentifierUtil.randomUUID());
                                    }else {
                                        List<TplHazardItemDto> item = projectItems.stream().filter(p -> p.getHinm().equals(cell.toString())).collect(Collectors.toList());
                                        if (item == null || item.size() == 0x0){
                                            itemDto.setHicd(IdentifierUtil.randomUUID());
                                        }else{
                                            itemDto.setHicd(item.get(0x0).getHicd());
                                        }
                                    }
                                    //String hicd = ChineseCharacterUtil.getPinyinInitial(cell.toString());
                                    //if (hicd.length() > 10) hicd = hicd.substring(0,10);
                                    //itemDto.setHicd(curentProject + "_" + hicd);
                                    itemDto.setHicd(IdentifierUtil.randomUUID());
                                    itemDto.setHinm(cell.toString());
                                    itemDto.setHpjtcd(curentProject);
                                    itemDto.setIsmajor(dto.getIsmajor());
                                }
                            }else if (columns.get(cell.getColumnIndex()).equals("一般危险源")){
                                if (StringUtils.isNotBlank(cell.toString())){
                                    itemDto.setDescription(cell.toString());
                                }
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("一般危险源")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                //判断导入方式如果是覆盖，重新生成清单代码
                                if (dto.getIscover()) {
                                    itemDto.setHicd(IdentifierUtil.randomUUID());
                                }else {
                                    List<TplHazardItemDto> item = projectItems.stream().filter(p -> p.getHinm().equals(cell.toString())).collect(Collectors.toList());
                                    if (item == null || item.size() == 0x0){
                                        itemDto.setHicd(IdentifierUtil.randomUUID());
                                    }else{
                                        itemDto.setHicd(item.get(0x0).getHicd());
                                    }
                                }
                                //String hicd = ChineseCharacterUtil.getPinyinInitial(cell.toString());
                                //if (hicd.length() > 10) hicd = hicd.substring(0,10);
                                //itemDto.setHicd(curentProject + "_" + hicd);
                                //itemDto.setHicd(IdentifierUtil.randomUUID());
                                itemDto.setHinm(cell.toString());
                                itemDto.setHpjtcd(curentProject);
                                itemDto.setIsmajor(dto.getIsmajor());
                            }
                        }else if (columns.get(cell.getColumnIndex()).equals("重大危险源")){
                            if (StringUtils.isNotBlank(cell.toString())){
                                //判断导入方式如果是覆盖，重新生成清单代码
                                if (dto.getIscover()) {
                                    itemDto.setHicd(IdentifierUtil.randomUUID());
                                }else {
                                    List<TplHazardItemDto> item = projectItems.stream().filter(p -> p.getHinm().equals(cell.toString())).collect(Collectors.toList());
                                    if (item == null || item.size() == 0x0){
                                        itemDto.setHicd(IdentifierUtil.randomUUID());
                                    }else{
                                        itemDto.setHicd(item.get(0x0).getHicd());
                                    }
                                }
                                //String hicd = ChineseCharacterUtil.getPinyinInitial(cell.toString());
                                //if (hicd.length() > 10) hicd = hicd.substring(0,10);
                                //itemDto.setHicd(curentProject + "_" + hicd);
                                //itemDto.setHicd(IdentifierUtil.randomUUID());
                                itemDto.setHinm(cell.toString());
                                itemDto.setHpjtcd(curentProject);
                                itemDto.setIsmajor(dto.getIsmajor());
                            }
                        }
                    }
                }
            }
            if (StringUtils.isNotBlank(itemDto.getHicd())) itemDtos.add(itemDto);
        }

        //保存危险源类别、项目、清单数据
        //当前所有类别下的项目
        //List<TplHazardProjectDto> categoryProjects = new ArrayList<>();
        ////当前所有项目下的清单
        //List<TplHazardItemDto> projectItems = new ArrayList<>();
        categoryProjects.clear();
        projectItems.clear();
        //保存类别：查询当前导则库的所有类别，判断要新增的每一个类别是否存在，不存在的才添加
        if (CollectionUtils.isNotEmpty(categoryDtos)){
            //List<TplHazardCategoryDto> dbCategories = hazardcategoryService.getDBCategories(dto.getHdbcd());
            if (dto.getIscover()){
                //覆盖：先删除已有的数据，再全部添加
                //删除指定导则库的类别（同时删除项目、清单）
                if (CollectionUtils.isNotEmpty(dbCategories)){
                    for (TplHazardCategoryDto categoryDto : dbCategories){
                        hazardcategoryService.deleteCategory(categoryDto.getHcatcd());
                    }
                    dbCategories = null;
                }
            }
            String addItemResult = "";//记录添加异常结果
            for (TplHazardCategoryDto categoryDto : categoryDtos){
                boolean catgoryIsExist = false;
                if (CollectionUtils.isNotEmpty(dbCategories)){
                    catgoryIsExist = dbCategories.stream().anyMatch(p -> p.getHcatnm().equals(categoryDto.getHcatnm()));
                }
                if (!catgoryIsExist){
                    String addResult = hazardcategoryService.addCategory(categoryDto,true);
                    if (addResult != null) addItemResult += addResult +"："+  categoryDto.getHcatcd() + ",";
                }
                //获取每个类别的项目，添加到categoryProjects中
                List<TplHazardProjectDto> projectDtoList = hazardprojectService.getCategoryProjects(categoryDto.getHcatcd());
                if (CollectionUtils.isNotEmpty(projectDtoList)) categoryProjects.addAll(projectDtoList);
            }
            if (StringUtils.isNotBlank(addItemResult))
                return QCUnifyReturnValue.Warn(addItemResult);
        }
        
        //保存项目：判断要新增的每一个项目是否存在，不存在的才添加
        if (CollectionUtils.isNotEmpty(projectDtos)){
            String addItemResult = "";//记录添加异常结果
            for (TplHazardProjectDto projectDto : projectDtos){
                boolean projectIsExist = false;
                if (CollectionUtils.isNotEmpty(categoryProjects)){
                    projectIsExist = categoryProjects.stream().anyMatch(p -> p.getHpjtnm().equals(projectDto.getHpjtnm()));
                }
                if (!projectIsExist){
                    String addResult = hazardprojectService.addProject(projectDto,true);
                    if (addResult != null) addItemResult += addResult +"："+  projectDto.getHpjtcd() + ",";
                }
                ////获取每个项目的清单，添加到projectItems中
                List<TplHazardItemDto> itemDtoList = hazarditemService.getProjectItems(projectDto.getHpjtcd());
                if (CollectionUtils.isNotEmpty(itemDtoList)) projectItems.addAll(itemDtoList);
            }
            if (StringUtils.isNotBlank(addItemResult))
                return QCUnifyReturnValue.Warn(addItemResult);
        }

        //保存清单：判断要新增的每一个清单是否存在，不存在的才添加
        if (CollectionUtils.isNotEmpty(itemDtos)){
            String addItemResult = "";//记录添加异常结果
            for (TplHazardItemDto itemDto : itemDtos){
                boolean itemIsExist = false;
                if (CollectionUtils.isNotEmpty(projectItems)){
                    itemIsExist = projectItems.stream().anyMatch(p -> p.getHinm().equals(itemDto.getHinm()));
                }
                if (!itemIsExist){
                    String addResult = hazarditemService.addItem(itemDto,true);
                    if (addResult != null) addItemResult += addResult +"："+ itemDto.getHicd() + ",";
                }
            }
            if (StringUtils.isNotBlank(addItemResult))
                return QCUnifyReturnValue.Warn(addItemResult);
        }
        
        return QCUnifyReturnValue.Success();
    }

    /**
     * 获取指定危险源导则库 - 同时支持@RequestParam与@PathVariable方式
     *
     * @param id 危险源导则库代码
     * @return TplHazardDbDto
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public  TplHazardDbDto getDB(String id) throws QCPromptException {
        if (StringUtils.isBlank(id)) throw new QCPromptException("危险源导则库代码不能为空");
        
        EhsTplHazarddb en = repository.selectById(id);
        if (Objects.nonNull(en)){
            return EhsTplHazarddbMapper.MAPPER.toDto(en);
        }
        return null;
    }

    /**
     * 新增危险源导则库
     *
     * @param dto 危险源导则库信息
     * @return 成功返回null，失败返回错误或提示信息
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public String addDB(TplHazardDbDto dto){
        //1.导则库信息判空
        if (dto == null) return QCUnifyReturnValue.Warn("危险源导则库信息不能为空");
        if (StringUtils.isBlank(dto.getHdbcd())) return QCUnifyReturnValue.Warn("导则库代码不能为空");
        if (StringUtils.isBlank(dto.getHdbnm())) return QCUnifyReturnValue.Warn("导则库名称不能为空");
        if (dto.getIsconstruction() == null) return QCUnifyReturnValue.Warn("是否为在建阶段不能为空");
        
        //2.判断导则库代码是否存在，存在则提示
        if (hdbcdIsExist(dto.getHdbcd())) return QCUnifyReturnValue.Warn("导则库代码已经存在");
        
        //3.dto -> entity
        EhsTplHazarddb en = EhsTplHazarddbMapper.MAPPER.toEntity(dto);
        
        //4.保存数据
        if (repository.insert(en) < 0x1){
            return QCUnifyReturnValue.Warn("保存失败");
        }
        return QCUnifyReturnValue.Success();
    }

    /**
     * 修改危险源导则库
     *
     * @param dto 危险源导则库信息
     * @return 成功返回null，失败返回错误或提示信息
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public String updateDB(TplHazardDbDto dto){
        //1.导则库信息判空
        if (dto == null) return QCUnifyReturnValue.Warn("危险源导则库信息不能为空");
        if (StringUtils.isBlank(dto.getHdbcd())) return QCUnifyReturnValue.Warn("导则库代码不能为空");
        if (StringUtils.isBlank(dto.getHdbnm())) return QCUnifyReturnValue.Warn("导则库名称不能为空");
        if (dto.getIsconstruction() == null) return QCUnifyReturnValue.Warn("是否为在建阶段不能为空");

        //2.判断导则库代码是否存在，不存在则提示
        if (!hdbcdIsExist(dto.getHdbcd())) return QCUnifyReturnValue.Warn("导则库代码不存在");

        //3.dto -> entity
        EhsTplHazarddb en = EhsTplHazarddbMapper.MAPPER.toEntity(dto);

        //4.保存数据
        repository.updateById(en);
        return QCUnifyReturnValue.Success();
    }

    /**
     * 删除危险源导则库
     * 同时删除类别、项目、清单
     *
     * @param id 危险源导则库代码
     * @return 成功返回null，失败返回错误或提示信息
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public String deleteDB(String id){
        if (StringUtils.isBlank(id)) return QCUnifyReturnValue.Warn("危险源导则库代码不能为空");
        
        //获取该危险源导则库下的类别
        LambdaQueryWrapper<EhsTplHazardcategory> categoryWrapper = new LambdaQueryWrapper<>();
        categoryWrapper.eq(EhsTplHazardcategory::getHdbcd,id);
        List<EhsTplHazardcategory> categories = hazardcategoryRepository.selectList(categoryWrapper);
        List<String> categoryIds = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(categories)){
            categoryIds = categories.stream().map(EhsTplHazardcategory::getHcatcd).collect(Collectors.toList());
        }
        
        //获取类别下的项目
        if (CollectionUtils.isNotEmpty(categoryIds)){
            LambdaQueryWrapper<EhsTplHazardproject> projectWrapper = new LambdaQueryWrapper<>();
            projectWrapper.in(EhsTplHazardproject::getHcatcd,categoryIds);
            List<EhsTplHazardproject> projects = hazardprojectRepository.selectList(projectWrapper);
            if (CollectionUtils.isNotEmpty(projects)){
                List<String> projectIds = projects.stream().map(EhsTplHazardproject::getHpjtcd).collect(Collectors.toList());
                //删除以上项目代码集合的清单
                if (CollectionUtils.isNotEmpty(projectIds)){
                    LambdaQueryWrapper<EhsTplHazarditem> itemWrapper = new LambdaQueryWrapper<>();
                    itemWrapper.in(EhsTplHazarditem::getHpjtcd,projectIds);
                    hazarditemRepository.delete(itemWrapper);
                }
                //删除该类别下的项目
                hazardprojectRepository.deleteBatchIds(projectIds);
            }
            //删除类别
            hazardcategoryRepository.deleteBatchIds(categoryIds);
        }
        
        //删除导则库
        repository.deleteById(id);
        return QCUnifyReturnValue.Success();
    }

    /**
     * 判断指定危险源导则库代码是否存在
     *
     * @param id 危险源导则库代码
     * @return true表示存在，false表示不存在
     * @author QuCheng Tech
     * @since 2024/5/24
     */
    public boolean hdbcdIsExist(String id){
        EhsTplHazarddb en = repository.selectById(id);
        if (Objects.nonNull(en)){
            return true;
        }
        return false;
    }
}
